/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package acceso.google;

import acceso.util.Escritor;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import javax.servlet.ServletException;
//import org.hsqldb.persist.HsqlProperties;
//import org.hsqldb.server.Server;
//import org.hsqldb.server.ServerAcl.AclFormatException;

/**
 *
 * @author romulogalindo
 */
public class migrador {
    public migrador(){
        try{
            java.util.Properties p = new java.util.Properties();
            p.load(new FileInputStream("/home/romulogalindo/google.properties"));
            
            String[] areas = p.getProperty("areas").split(";");
            HashMap n = new HashMap();
            for(int i = 0;i < areas.length;i++){
                String[] area = areas[i].split(",");
                n.put(area[0],area[1]);
            }
            //n.put(n, n)
            System.out.println("AREAS:"+areas);
            
            Object[] s = p.stringPropertyNames().toArray();
            for(int i = 0 ; i < s.length;i++){
                String pro = (String) s[i];
                //System.out.println(">>>"+pro+"--"+n.get(pro)+"--------------------------" );
                System.out.println("insert into google.tp_areas(id_areas,no_areas) values("+pro+",'"+n.get(pro)+"');" );
                if(!pro.contentEquals("areas")){
                    String[] ele = p.getProperty(pro).split(";");
                    if(pro.contentEquals("0")){
                        for(int o=ele.length-1; o >= 0;o--){
                            String[] d = ele[o].split(",");
                            //System.out.println("|"+d[0] +"|"+d[1]+"|"+d[2]+"|");
                            System.out.println("insert into google.tp_puntos(no_latitu,no_longit,id_areas) values('"+d[1]+"','"+d[2]+"',"+3+");");
                        }
                    }/*else if(pro.contentEquals("6")){
                        for(int o=ele.length-1; o >= 0;o--){
                            String[] d = ele[o].split(",");
                            //System.out.println("|"+d[0] +"|"+d[1]+"|"+d[2]+"|");
                            System.out.println("insert into google.tp_puntos(no_latitu,no_longit,id_areas) values('"+d[1]+"','"+d[2]+"',"+3+");");
                        }
                    }else if(pro.contentEquals("4")){
                        for(int o=0 ; o < ele.length;o++){
                            String[] d = ele[o].split(",");
                            //System.out.println("|"+d[0] +"|"+d[1]+"|"+d[2]+"|");
                            System.out.println("insert into google.tp_puntos(no_latitu,no_longit,id_areas) values('"+d[1]+"','"+d[2]+"',"+3+");");
                        }
                    }else if(pro.contentEquals("2") | pro.contentEquals("7") | pro.contentEquals("8") | pro.contentEquals("9") | pro.contentEquals("10")){
                        for(int o=0 ; o < ele.length;o++){
                            String[] d = ele[o].split(",");
                            //System.out.println("|"+d[0] +"|"+d[1]+"|"+d[2]+"|");
                            System.out.println("insert into google.tp_puntos(no_latitu,no_longit,id_areas) values('"+d[1]+"','"+d[2]+"',"+3+");");
                        }
                    }*/else{
                        for(int o=0 ; o < ele.length;o++){
                            String[] d = ele[o].split(",");
                            //System.out.println("|"+d[0] +"|"+d[1]+"|"+d[2]+"|");
                            //System.out.println("insert into google.tp_puntos(no_latitu,no_longit,id_areas) values('"+d[1]+"','"+d[2]+"',"+pro+");");
                            System.out.println("insert into google.tp_puntos(no_latitu,no_longit,id_areas) values('"+d[1]+"','"+d[2]+"',"+(Integer.parseInt(pro)+58)+");");
                        }
                    }
                    
                }else{
                    //System.out.println(">>>"+p.getProperty(pro)+"------" );
                }
                System.out.println("-----------------------------------");
            }
        }catch(Exception ep){}
    }
    
    public migrador(int mi){
        Connection con = null;//LOCAL
        Statement st = null;
        //CONEXION
        try{
            Class.forName("org.postgresql.Driver");
            con = DriverManager.getConnection("jdbc:postgresql://192.168.1.107:5439/postgres","google","1234");
            System.out.println("Connexion exitosa a la base de datos");
            st = con.createStatement();
        }catch(Exception ep){
            System.out.println("Error levantado servlet google:"+ep.getMessage());
        }
        
        
        //COEXION 2
        
        Connection con2 = null;//LOCAL
        Statement st2 = null;
        try{
            Class.forName("org.postgresql.Driver");
            con2 = DriverManager.getConnection("jdbc:postgresql://192.168.1.11:5439/WFACR","rgalindo","W41t3Kn1g4t");
            System.out.println("Connexion exitosa a la base de datos2");
            st2 = con2.createStatement();
        }catch(Exception ep){
            System.out.println("Error levantado servlet google:"+ep.getMessage());
        }
        
        
        
        //TRABAJO
        
        List l = new ArrayList(); //<--LISTA DE TODAS LA AREA ORIGEN
        
        try{
            ResultSet rpta = st.executeQuery("select * from google.tp_areas order by id_areas");
            while(rpta.next()){
                l.add(new Areas(rpta.getInt(1), rpta.getString(2)));
            }
        }catch(Exception ep){
            
        }
        
        
        for(int i = 0 ; i < l.size() ; i ++){
            Areas area = (Areas)l.get(i);
            List l_puntos = new ArrayList();
            try{
                ResultSet rpta = st.executeQuery("select * from google.tp_puntos where id_areas = "+area.getId_areas()+" order by id_puntos;");
                while(rpta.next()){
                    l_puntos.add(new Puntos(rpta.getInt(1), rpta.getString(2),rpta.getString(3),rpta.getInt(4)));
                }
            }catch(Exception ep){

            }

            System.out.println("===AREA : "+area.getId_areas() +" - "+area.getNo_areas());
            /*for(int o = 0 ; o < l_puntos.size();o++){
                Puntos punto = (Puntos)l_puntos.get(o);
                System.out.println("\t===PUNTO : AREA:"+punto.getId_areas() +", ORDEN:"+punto.getId_puntos()+", LAT:"+punto.getNo_latitu()+", LON:"+punto.getNo_longit());
            }*/
            System.out.println("\n\n");
            
            List l_id_puntos = new ArrayList();
            try{
                ResultSet rpta = st2.executeQuery("insert into georef.tczongeo(no_zongeo, ti_zongeo) values('"+area.getNo_areas()+"',1::smallint) returning id_zongeo;");
                String id_area = "";
                while(rpta.next()){
                    id_area = rpta.getString(1);
                }
                
                for(int o = 0 ; o < l_puntos.size();o++){
                    Puntos punto = (Puntos)l_puntos.get(o);
                    System.out.println("\t===PUNTO : AREA:"+punto.getId_areas() +", ORDEN:"+punto.getId_puntos()+", LAT:"+punto.getNo_latitu()+", LON:"+punto.getNo_longit());
                    
                    rpta = st2.executeQuery("insert into georef.tcpungeo(nu_geolat, nu_geolon) values("+punto.getNo_latitu()+","+punto.getNo_longit()+") returning id_pungeo;");
                    String id_punto = "";
                    while(rpta.next()){
                        id_punto = rpta.getString(1);
                    }
                    System.out.println("--->punto ID:"+id_punto);
                    
                    l_id_puntos.add(""+id_punto);
                    
                    rpta = st2.executeQuery("insert into georef.tczonpun(id_zongeo, id_pungeo, or_zonpun) values("+id_area+","+id_punto+","+(o+1)+") returning id_zongeo,id_zongeo,or_zonpun;");
                    
                    while(rpta.next()){
                        System.out.println("--->"+rpta.getString(1)+" | "+ rpta.getString(2) + " | "+ rpta.getString(3));
                    }
                    
                    
                    
                }
            }catch(Exception ep){
                System.out.println("Algo paso: "+ep.getMessage());
            }
        }
        
    }
    
    public migrador(int a, int b) throws ServletException{
//        try {
//            System.out.println("Starting Database");
//            HsqlProperties p = new HsqlProperties();
//            //p.setProperty("server.database.0", "file:/opt/db/crm");
//            p.setProperty("server.dbname.0", "rankana");
//            p.setProperty("server.port", "9001");
//            org.hsqldb.server.Server server = new Server();
//            server.setProperties(p);
//            server.setLogWriter(null); // can use custom writer
//            server.setErrWriter(null); // can use custom writer
//            server.start();
//        } catch (AclFormatException afex) {
//            throw new ServletException(afex);
//        } catch (IOException ioex) {
//            throw new ServletException(ioex);
//        }

    }
    
    public static void main(String[] args) throws ServletException{
        new migrador(0,0);
    }
}
